#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection
from web.dao.base_dao import BaseDao
from web.models.unite_relative_price_index_date import UniteRelativePriceIndexDate

"""
UniteRelativePriceIndexDate的dao类
"""


class UniteRelativePriceIndexDateDao(BaseDao):
    model_class = UniteRelativePriceIndexDate

    ########################################### 全量计算数据 ########################################
    def write_date_basic_data(self):
        """
        计算日线级别全部close_price字段
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.write_basic_data()")

    ########################################### 增量计算数据 ########################################
    def write_date_basic_data_by_date(self, date):
        """
        计算日线级别，某一个交易日，统一相对价格指数的close_price字段
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_U_R_PRICE_INDEX.write_basic_data_by_date", [date])

    ########################################### 全量计算MA数据 ########################################
    def write_date_ma_data(self):
        """
        计算日线级别全部ma字段
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.CALCULATE_FIVE()")
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.CALCULATE_TEN()")
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.CALCULATE_TWENTY()")
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.CALCULATE_SIXTY()")
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.CALCULATE_ONEHUNDREDTWENTY()")
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.CALCULATE_TWOHUNDREDFIFTY()")

    ########################################### 增量计算MA数据 ########################################
    def write_date_ma_data_by_date(self, date):
        """
        计算日线级别，某一个交易日，统一相对价格指数的ma字段
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_U_R_PRICE_INDEX.WRITE_MOVING_AVERAGE_BY_DATE", [date])

    ########################################### 全量计算variance数据 ########################################
    def write_date_variance_data(self):
        """
        计算日线级别全部variance字段
        """

        with connection.cursor() as cursor:
            cursor.execute("CALL PKG_U_R_PRICE_INDEX.WRITE_VARIANCE()")

    ########################################### 增量计算varianceA数据 ########################################
    def write_date_variance_data_by_date(self, date):
        """
        计算日线级别，某一个交易日，统一相对价格指数的variance字段
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_U_R_PRICE_INDEX.WRITE_VARIANCE_BY_DATE", [date])

    ############################################## 创建图片 ###########################################
    def average_unite_relative_price_index_group_by_transaction_date(self, begin_date, end_date):
        """
        根据开始时间和结束时间，计算每个交易日的平均统一相对价格指数
        """

        with connection.cursor() as cursor:
            # cursor.execute("select t.transaction_date, avg(t.unite_relative_price_index) "
            #                "from commodity_future_date_data t "
            #                "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
            #                "group by t.transaction_date "
            #                "order by t.transaction_date asc", (begin_date, end_date))
            cursor.execute("select t.transaction_date, avg(t.close_price) "
                           "from unite_relative_price_index_d t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd') "
                           "group by t.transaction_date "
                           "order by t.transaction_date asc", (begin_date, end_date))
            five_date_tuple = cursor.fetchall()
            return five_date_tuple

    def find_by_transaction_date(self, transaction_date):
        """
        根据参数transaction_date，查找记录
        """

        filter_dict = {'transaction_date': transaction_date}
        return self.find_one(filter_dict, dict(), list())

    def find_average_variance20_between_transaction_date(self, begin_date: str, end_date: str) -> float:
        """
        查询在开始时间和结束时间之间的记录，并按照variance20平均值
        """

        with connection.cursor() as cursor:
            cursor.execute("select avg(t.variance20) "
                           "from unite_relative_price_index_d t "
                           "where t.transaction_date between to_date(%s,'yyyy-mm-dd') and to_date(%s,'yyyy-mm-dd')", (begin_date, end_date))
            _tuple = cursor.fetchall()
            return _tuple[0][0]
            # if _tuple is not None and len(_tuple) > 0:
            #     unite_relative_price_index_date_list: list[UniteRelativePriceIndexDate] = list()
            #     for t in _tuple:
            #         unite_relative_price_index_date: UniteRelativePriceIndexDate = UniteRelativePriceIndexDate()
            #         unite_relative_price_index_date.id = t[0]
            #         unite_relative_price_index_date_list.append(unite_relative_price_index_date)
            #     return unite_relative_price_index_date_list
            # else:
            #     None
